<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Report
 *
 * @author Massri
 */
class Application_Model_Report {

    //put your code here
    private $_db;

    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    
    function getTotalSaldo() {

        $sql = "select FORMAT(sum(balance), 2) as balance
                from accounts";

        $resultA = $this->_db->fetchRow($sql);

        $sql = "select sum(amt1) as amt, debkred from (
                select trans_dtm, acc_num1, FORMAT(amt1,2) as amt_rp, amt1,
                notes_txt as descr,
                mutation_type as debkred
                from v_transaction 
                ) ww group by debkred";

        $resultB = $this->_db->fetchAll($sql);
        $num = count($resultB);

        $debet = 0;
        $kredit = 0;

        if ($num > 0){

            for($k = 0;$k<$num;$k++) {
                if ($resultB[$k]['debkred'] == 'D'){
                    $debet = $resultB[$k]['amt'];
                }else{
                    $kredit = $resultB[$k]['amt'];
                }
            }
        }

        $arrDebKred = array('debet' => $debet, 'kredit' => $kredit);

        $result = array_merge($resultA, $arrDebKred);

        return $result;

    }

    function getMutasi($transType, $date1, $date2, $start, $limit, $noAkun="") {

        $sql = "select count(1) as total, FORMAT(sum(amt1),2) as jumlah
                from v_transaction where is_display = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        if ($noAkun != ""){
            $sql .= " and (acc_num1 = '$noAkun' or acc_num2 = '$noAkun') ";
        }

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);

       
        $sql = "select trans_dtm, acc_num1, 
                FORMAT(if(trans_type_code in ('3001', '3008'), amt2, amt1),2) as amt_rp,
                amt1,
                notes_txt as descr,
                mutation_type as debkred,
                if(mutation_type = 'D', FORMAT(amt1,2), 0) as debit, if(mutation_type = 'K', FORMAT(amt1,2), 0) as kredit,
                account_num, account_name
                from v_transaction where is_display = 'Y' 
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        if ($noAkun != ""){
            $sql .= " and (acc_num1 = '$noAkun' or acc_num2 = '$noAkun') ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        
        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }

    public function getAccounts() {

        $select = $this->_db->select();
        $select->from('accounts');
        $select->order( 'account_name' );

        $stmt = $select->query();
        $result = $stmt->fetchAll();

        return $result;

    }

    function getAccountList($accNum, $start, $limit) {

        // Get AccNum
        $sql = "select count(1) as total
                from accounts where account_num like '%$accNum%'";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);


        $sql = "select account_num
                ,account_name
                ,bank_name
                ,bank_account_no
                ,FORMAT(balance,2) as balance
                ,'IDR' as money
                from accounts where account_num like '%$accNum%' order by account_name ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        //echo $sql;
        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }
    
    function getTotalBalance($key) {

        $sql = "select balance, FORMAT(balance,0) as balance2
                from accounts
                where account_num = '$key'";

        $result = $this->_db->fetchRow($sql);
        return $result;
    }

    function isExistAccount($accNum){
        $sql = "select count(1) as cnt
                from accounts
                where account_num = '$accNum'";

        $row = $this->_db->fetchRow($sql);

        $result = array("cnt" => $row["cnt"]);

        return $result;
    }

       
    function getTrackingTransaction($accNum, $transType, $date1, $date2, $start, $limit) {

        $sql = "select count(1) as total, FORMAT(sum(amt1),2) as jumlah
                from v_transaction where is_display = 'Y' and is_external = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";


        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        //echo $sql;

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);


        $sql = "select trans_dtm, acc_num1,
                notes_txt as descr, external_param, external_resp, 
                account_num, account_name
                from v_transaction where is_display = 'Y' and is_external = 'Y'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){
            $sql .= " and acc_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }
}
